/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Etbl_Users;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mrteo
 */
public class DALtbl_Users extends BaseConnection {
    CallableStatement clbStm=null;
    ResultSet rs=null;
    public boolean addtbl_Users(Etbl_Users obj){
        try {
            getConnection();
            clbStm=cnn.prepareCall("{call tbl_Users_add(?,?,?,?,?,?,?,?,?,?)}");
            clbStm.setInt("userId", obj.getUserId());
            clbStm.setString("fullName",obj.getFullName());
            clbStm.setString("gender",obj.getGender());   
            clbStm.setString("address", obj.getAddress());
            clbStm.setString("phone", obj.getPhone());
            clbStm.setString("nationality", obj.getNationality());
            clbStm.setString("email", obj.getEmail());
            clbStm.setString("userName", obj.getUserName());
            clbStm.setString("passWord", obj.getPassWord());
            clbStm.setInt("role", obj.getRole());
            clbStm.execute();
            clbStm.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    public boolean edittbl_Users(Etbl_Users obj){
        try {
            getConnection();
            clbStm=cnn.prepareCall("{call tbl_Users_update(?,?,?,?,?,?,?,?,?,?)}");
            clbStm.setInt("userId", obj.getUserId());
            clbStm.setString("fullName",obj.getFullName());
            clbStm.setString("gender",obj.getGender());   
            clbStm.setString("address", obj.getAddress());
            clbStm.setString("phone", obj.getPhone());
            clbStm.setString("nationality", obj.getNationality());
            clbStm.setString("email", obj.getEmail());
            clbStm.setString("userName", obj.getUserName());
            clbStm.setString("passWord", obj.getPassWord());
            clbStm.setInt("role", obj.getRole());
            clbStm.execute();
            rs.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    public boolean deletetbl_Users(int userId){
        try {
            getConnection();
            clbStm=cnn.prepareCall("{call tbl_Users_delete(?)}");
            clbStm.execute();
            clbStm.close();
            releaseConnection();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public Etbl_Users getOnetbl_Users(int userId){
          Etbl_Users obj=new Etbl_Users();
          try {
            getConnection();
            clbStm=cnn.prepareCall("{call tbl_Users_get(?)}");
            clbStm.setInt("userId",userId);              
            rs=clbStm.executeQuery();            
            while(rs.next()){                            
                obj.setUserId(rs.getInt("userId"));
                obj.setFullName(rs.getString("fullName"));
                obj.setGender(rs.getString("gender"));
                obj.setAddress(rs.getString("address"));
                obj.setPhone(rs.getString("phone")); 
                obj.setNationality(rs.getString("nationality"));
                obj.setEmail(rs.getString("email"));
                obj.setUserName(rs.getString("userName"));
                obj.setPassWord(rs.getString("passWord"));
                obj.setRole(rs.getInt("role"));
            }   
            clbStm.close();
            rs.close();
            releaseConnection();
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
            
        }finally{
              return obj;
          }
      }
    
    public ArrayList getListUsers(String orderBy, String ASCorDESC, String WhereClause, int PagingIndex, int PagingSize){
        try {
            ArrayList UsersList=new ArrayList();
            getConnection();
            clbStm=cnn.prepareCall("{call tbl_Users_getpaged(?,?,?,?,?)}");
            clbStm.setString("OrderBy", orderBy);
            clbStm.setString("TangGiam", ASCorDESC);
            clbStm.setString("WhereClause", WhereClause);
            clbStm.setInt("PageIndex", PagingIndex);
            clbStm.setInt("PageSize", PagingSize);
            rs=clbStm.executeQuery();
            DALTotalPageIndex.getTotalPage("tbl_Users", WhereClause, PagingSize);
            while(rs.next()){
                Etbl_Users obj=new Etbl_Users();
                obj.setUserId(rs.getInt("userId"));
                obj.setFullName(rs.getString("fullName"));
                obj.setGender(rs.getString("gender"));
                obj.setAddress(rs.getString("address"));
                obj.setPhone(rs.getString("phone")); 
                obj.setNationality(rs.getString("nationality"));
                obj.setEmail(rs.getString("email"));
                obj.setUserName(rs.getString("userName"));
                obj.setPassWord(rs.getString("passWord"));
                obj.setRole(rs.getInt("role"));
                UsersList.add(obj);
            }
            clbStm.close();
            rs.close();
            releaseConnection();
            return UsersList;
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    public Etbl_Users CheckLogin(String UserName, String PassWord){
        Etbl_Users objUser= new Etbl_Users();
        try {
            getConnection();
            clbStm=cnn.prepareCall("{call Check_Login_sp(?,?)}");
            clbStm.setString("userName",UserName);
            clbStm.setString("passWord",PassWord);
            rs=clbStm.executeQuery();
            if(rs.next()){                
                objUser.setUserId(rs.getInt("userId"));
                objUser.setFullName(rs.getString("fullName"));
                objUser.setGender(rs.getString("gender"));
                objUser.setAddress(rs.getString("address"));
                objUser.setPhone(rs.getString("phone")); 
                objUser.setNationality(rs.getString("nationality"));
                objUser.setEmail(rs.getString("email"));
                objUser.setUserName(rs.getString("userName"));
                objUser.setRole(rs.getInt("role"));
            }
            else{
                objUser.setUserId(-1);
                objUser.setFullName(null);
                objUser.setGender(null);
                objUser.setAddress(null);
                objUser.setPhone(null); 
                objUser.setNationality(null);
                objUser.setEmail(null);
                objUser.setUserName(null);
                objUser.setRole(-1);
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(DALtbl_Users.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally{
            return objUser;
        }
    }
}
